package com.chart.demo.db;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DbFactory {
	
	public static String username="sa";
	public static String password="sa";
	public static String ip="localhost";
	public static String port="1433";
	
	public static String streetUsername="sa";
	public static String streetPassword="123456";
	public static String streetIp="localhost";
	public static String streetPort="1433";
	
	public static Connection getConnectionToStreet() {
		Connection conn = null;		
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			String connectionUrl = "jdbc:sqlserver://"+streetIp+":"+streetPort+";"
					+ "databaseName=YiMenShi2009;user=" + streetUsername + ";password="
					+ streetPassword;
			conn = DriverManager.getConnection(connectionUrl);
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return conn;
	}
	
//	con = DriverManager.getConnection("jdbc:sqlserver://172.16.5.2;instanceName=SQL2005; DatabaseName=EAS60_Demo", "sa","sa");
//	public static Connection getConnectionToStreet() {
//		Connection conn = null;		
//		try {
//			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//			String connectionUrl = "jdbc:sqlserver://"+streetIp+";"
//					+ "instanceName=YiMenShi2009;databaseName=YiMenShi2009;user=" + streetUsername + ";password="
//					+ streetPassword;
//			conn = DriverManager.getConnection(connectionUrl);
//		} catch (Exception e) {
//			e.printStackTrace();
//			return null;
//		}
//		return conn;
//	}
	public static Connection getConnectionToStreetTest() {
		Connection conn = null;		
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			String connectionUrl = "jdbc:sqlserver://localhost:1433;"
					+ "databaseName=YiMenShi2009;user=yms09;password=1q2w3e4r";
			conn = DriverManager.getConnection(connectionUrl);
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return conn;
	}
	
	
	public static Connection getConnection() {
		Connection conn = null;		
		try {
			Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
			String connectionUrl = "jdbc:microsoft:sqlserver://"+ip+":"+port+";"
					+ "databaseName=ShangChuan;user=" + username + ";password="
					+ password;
			System.out.println(connectionUrl);
			conn = DriverManager.getConnection(connectionUrl);
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return conn;
	}
	
	public static Connection getConnectionWithSelect() {
		Connection conn = null;
		try {
			Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
			String connectionUrl = "jdbc:microsoft:sqlserver://"+ip+":"+port+";"
					+ "databaseName=SelectShangChuan;user=" + username + ";password="
					+ password;
			conn = DriverManager.getConnection(connectionUrl);
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return conn;
	}

	public void findUserCountBalance() {

		String sqlx = "select * from County";
		try {
			Connection conn = getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sqlx);
			System.out.println("findUserCountBalance=" + sqlx);
			if (rs.next()) {
				System.out.println(rs.toString());
			}
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		}
	}

	// CREATE Procedure StreetDayCountTop20ByCounty
	// @Day VARCHAR(30),
	// @County VARCHAR(30)
	// AS
	// select Top 20
	// StreetScene.StreetSceneName,StreetScene.StreetSceneCode,a.Total from
	// StreetScene,
	// (select BelongStreetSceneCode,sum(Total)as Total from CarryAffairsInfo
	// where InfoDay=Convert(DateTime,@Day) AND BelongCountyCode=@County
	// group by BelongStreetSceneCode)as a
	// where a.BelongStreetSceneCode=StreetScene.StreetSceneCode
	// order by a.Total DESC
	// GO

	public void testCall() {

		try {
			Connection conn = getConnection();
			CallableStatement cs = conn
					.prepareCall("{call StreetDayCountTop20ByCounty(?,?)}");
			cs.setString(1, "2006-8-31");
			cs.setString(2, "ZB");
			ResultSet rs = cs.executeQuery();
			while (rs.next()) {
				System.out.println(rs.getString(1) + "    " + rs.getString(2)+ "    " + rs.getInt(3));
			}
			rs.close();
			cs.close();
			conn.close();
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		}
	}

	public static void main(String[] a) {
		new DbFactory().testCall();
	}
}
